/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.Penelitian;
import Entity.SumberPendanaan;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import library.Koneksi;

/**
 *
 * @author user
 * model untuk menangani operasi database berkaitan dengan data penelitian
 */
public class DaftarPenelitian {

    Penelitian penelitian;

    //method digunakan untuk mencetak rekapitulasi data penelitian
    public ArrayList cetakRekapPenelitian(String byPeneliti, String byJudul, String bySbrDana, String byPeriodeMulai, String byPeriodeSelesai) {

        String sql = "";

        if (!byPeneliti.isEmpty()) {
            sql = "SELECT SUMBER_PENDANAAN, YEAR(TANGGAL_PENELITIAN_DIMULAI) AS TAHUN, COUNT(penelitian_dosen.NO_PENELITIAN) AS JUMLAH "
                + "FROM sumber_pendanaan, penelitian_dosen, dosen_peneliti "
                + "WHERE penelitian_dosen.ID_SUMBER_PENDANAAN = sumber_pendanaan.ID_SUMBER_PENDANAAN "
                + "AND penelitian_dosen.NO_PENELITIAN = dosen_peneliti.NO_PENELITIAN "
                + "AND dosen_peneliti.ID_USER = '"+byPeneliti+"' AND STATUS_PENELITIAN = '1'";
        } else{
            sql = "SELECT SUMBER_PENDANAAN, YEAR(TANGGAL_PENELITIAN_DIMULAI) AS TAHUN, COUNT(NO_PENELITIAN) AS JUMLAH "
                + "FROM sumber_pendanaan, penelitian_dosen "
                + "WHERE penelitian_dosen.ID_SUMBER_PENDANAAN = sumber_pendanaan.ID_SUMBER_PENDANAAN "
                + "AND STATUS_PENELITIAN = '1'";
        }
        
        if (!byJudul.isEmpty()) {
            sql = sql + " AND penelitian_dosen.JUDUL_PENELITIAN LIKE '%" + byJudul + "%'";
        }
        
        if (!bySbrDana.isEmpty()) {
            sql = sql + " AND sumber_pendanaan.ID_SUMBER_PENDANAAN = " + bySbrDana;
        }
        
        if(!byPeriodeMulai.isEmpty() && !byPeriodeSelesai.isEmpty()){
            sql = sql+" AND YEAR(TANGGAL_PENELITIAN_DIMULAI) BETWEEN '" + byPeriodeMulai + "' AND '" + byPeriodeSelesai + "' ";
        }
        
        sql = sql + " GROUP BY sumber_pendanaan.sumber_pendanaan";
        //2012, 2011, 2010, 2009
        ArrayList list = new ArrayList();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();
            System.out.println(sql);
            st = conn.prepareStatement(sql);
            rs = st.executeQuery();
            int i = 1;
            while (rs.next()) {
                Map map = new HashMap();
                map.put("no", i);
                map.put("sumberDana", rs.getString("SUMBER_PENDANAAN"));
                map.put("tahun", rs.getString("TAHUN"));
                map.put("jumlah", rs.getString("JUMLAH"));

                list.add(map);
                i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;


    }

    //method digunakan untuk mengambil daftar penelitian berdasarkan peneliti, judul, sumber pendanaan, dan periode pelaksanaan dan status penelitian
    public List<Penelitian> daftarPenelitianDosen(String byPeneliti, String byJudul, String bySbrDana, String byPeriodeMulai, String byPeriodeSelesai, String byStatus) {
        List<Penelitian> daftarPenelitian = new ArrayList<Penelitian>();
        
        String sql = "";

        if (!byPeneliti.isEmpty()) {
            sql = sql + "SELECT * FROM sumber_pendanaan, penelitian_dosen, user, dosen, dosen_peneliti "
                + "WHERE penelitian_dosen.ID_SUMBER_PENDANAAN = sumber_pendanaan.ID_SUMBER_PENDANAAN AND penelitian_dosen.NO_PENELITIAN = dosen_peneliti.NO_PENELITIAN AND "
                + "dosen_peneliti.ID_USER = dosen.ID_USER AND dosen.ID_USER = user.ID_USER "
                    + "AND dosen_peneliti.ID_USER = '" + byPeneliti + "' ";
        } else{
            sql = sql + "SELECT * FROM sumber_pendanaan, penelitian_dosen, user, dosen, dosen_peneliti "
                + "WHERE penelitian_dosen.ID_SUMBER_PENDANAAN = sumber_pendanaan.ID_SUMBER_PENDANAAN AND penelitian_dosen.NO_PENELITIAN = dosen_peneliti.NO_PENELITIAN AND "
                + "dosen_peneliti.ID_USER = dosen.ID_USER AND dosen.ID_USER = user.ID_USER "
                    + "AND dosen_peneliti.STATUS_DOSEN = '1' ";
        }        
        
        if (!byJudul.isEmpty()) {
            sql = sql + "AND penelitian_dosen.JUDUL_PENELITIAN LIKE '%" + byJudul + "%' ";
        }
        
        if (!byStatus.isEmpty()) {
            sql = sql + "AND penelitian_dosen.STATUS_PENELITIAN = '" + byStatus + "' ";
        }

        if (!byPeriodeMulai.isEmpty()) {
            sql = sql + "AND YEAR(penelitian_dosen.TANGGAL_PENELITIAN_DIMULAI) BETWEEN '" + byPeriodeMulai + "' AND '" + byPeriodeSelesai + "' ";
        }

        sql = sql + "ORDER BY penelitian_dosen.TANGGAL_PENELITIAN_DIMULAI DESC";
        System.out.println(sql);
        PreparedStatement st = null;
        ResultSet rs = null;
        Connection conn = Koneksi.getKoneksi();
        PreparedStatement st2 = null;
        ResultSet rs2 = null;
        try {

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while (rs.next()) {
                int noPenelitian = rs.getInt("NO_PENELITIAN");

                String sql2 = "SELECT * FROM sumber_pendanaan, penelitian_dosen, user, dosen, dosen_peneliti "
                        + "WHERE penelitian_dosen.ID_SUMBER_PENDANAAN = sumber_pendanaan.ID_SUMBER_PENDANAAN AND penelitian_dosen.NO_PENELITIAN = dosen_peneliti.NO_PENELITIAN AND "
                        + "dosen_peneliti.ID_USER = dosen.ID_USER AND dosen.ID_USER = user.ID_USER "
                        + "AND dosen_peneliti.STATUS_DOSEN = '1' AND penelitian_dosen.NO_PENELITIAN=" + noPenelitian;
                if(!bySbrDana.isEmpty()){
                    sql2 = sql2 + " AND sumber_pendanaan.SUMBER_PENDANAAN = "+bySbrDana;
                }
                
                st2 = conn.prepareStatement(sql2);
                rs2 = st2.executeQuery();
                System.out.println("kedua:"+sql2);
                while (rs2.next()) {

                    this.penelitian = new Penelitian();

                    this.penelitian.setNoPenelitian(rs2.getInt("NO_PENELITIAN"));
                    this.penelitian.setJudulPenelitian(rs2.getString("JUDUL_PENELITIAN"));
                    this.penelitian.setDanaDiterima(rs2.getString("BESAR_DANA_DITERIMA"));
                    this.penelitian.setKetuaPeneliti(rs2.getString("NAMA_USER"));
                    this.penelitian.setIdKetuaPeneliti(rs2.getString("ID_USER"));
                    this.penelitian.setTanggalPenelitianDimulai(rs2.getString("TANGGAL_PENELITIAN_DIMULAI"));
                    this.penelitian.setTanggalPenelitianSelesai(rs2.getString("TANGGAL_PENELITIAN_SELESAI"));
                    this.penelitian.setLampiranLaporan(rs2.getString("LAMPIRAN_LAPORAN_PENELITIAN"));
                    this.penelitian.setUkuranLampiranLaporan(rs2.getString("UKURAN_LAMPIRAN_LAPORAN_PENELITIAN"));
                    this.penelitian.setStatusPenelitian(rs2.getString("STATUS_PENELITIAN"));
                    
                    SumberPendanaan sumberPendanaan = new SumberPendanaan();
                    sumberPendanaan.setIdSumberPendanaan(rs2.getInt("ID_SUMBER_PENDANAAN"));
                    sumberPendanaan.setSumberPendanaan(rs2.getString("SUMBER_PENDANAAN"));

                    this.penelitian.setSumberDana(sumberPendanaan);
                    
                    daftarPenelitian.add(this.penelitian);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
                if (st2 != null) {
                    st2.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
                if (rs2 != null) {
                    rs2.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarPenelitian;
    }

    //method digunakan untuk menambah data penelitian
    public boolean tambahDataPenelitian(Penelitian penelitian) {
        boolean hasil = false;
        this.penelitian = penelitian;
        String sql = "insert into penelitian_dosen (ID_SUMBER_PENDANAAN, JUDUL_PENELITIAN, TANGGAL_PENELITIAN_DIMULAI, "
                + "TANGGAL_PENELITIAN_SELESAI, BESAR_DANA_DITERIMA, LAMPIRAN_LAPORAN_PENELITIAN, UKURAN_LAMPIRAN_LAPORAN_PENELITIAN, "
                + "TIPE_FILE_LAMPIRAN_LAPORAN_PENELITIAN, TANGGAL_UPLOAD_LAPORAN, STATUS_PENELITIAN, PENGUPLOAD, UNIQUEKEY) "
                + "value (" + this.penelitian.getSumberDana().getIdSumberPendanaan()
                + ",'" + this.penelitian.getJudulPenelitian()
                + "','" + this.penelitian.getTanggalPenelitianDimulai()
                + "','" + this.penelitian.getTanggalPenelitianSelesai()
                + "','" + this.penelitian.getDanaDiterima()
                + "','" + this.penelitian.getLampiranLaporan()
                + "','" + this.penelitian.getUkuranLampiranLaporan()
                + "','" + this.penelitian.getTipeLampiranLaporan()
                + "','" + this.penelitian.getTanggalUploadLaporan()
                + "','" + this.penelitian.getStatusPenelitian()
                + "','" + this.penelitian.getPengupload().getIdUser()                
                + "','" + this.penelitian.getUniqueKey()
                + "')";
        System.out.println(sql);
        Connection conn = Koneksi.getKoneksi();
        PreparedStatement st = null;
        try {

            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        return hasil;
    }

    //method digunakan untuk mengambil detail data penelitian
    public Penelitian detailPenelitian(Penelitian penelitian) {

        String sql = "SELECT * FROM sumber_pendanaan, penelitian_dosen, user, dosen, dosen_peneliti "
                + "WHERE penelitian_dosen.ID_SUMBER_PENDANAAN = sumber_pendanaan.ID_SUMBER_PENDANAAN AND penelitian_dosen.NO_PENELITIAN = dosen_peneliti.NO_PENELITIAN AND "
                + "dosen_peneliti.ID_USER = dosen.ID_USER and dosen.ID_USER = user.ID_USER "
                + "AND dosen_peneliti.STATUS_DOSEN = '1' and penelitian_dosen.NO_PENELITIAN=" + penelitian.getNoPenelitian();
        PreparedStatement st = null;
        Connection conn;
        ResultSet rs = null;
        try {

            conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while (rs.next()) {
                this.penelitian = new Penelitian();

                SumberPendanaan sumberPendanaan = new SumberPendanaan();
                sumberPendanaan.setIdSumberPendanaan(rs.getInt("ID_SUMBER_PENDANAAN"));
                sumberPendanaan.setSumberPendanaan(rs.getString("SUMBER_PENDANAAN"));

                this.penelitian.setNoPenelitian(rs.getInt("NO_PENELITIAN"));
                this.penelitian.setJudulPenelitian(rs.getString("JUDUL_PENELITIAN"));
                this.penelitian.setKetuaPeneliti(rs.getString("NAMA_USER") + ", NIP : " + rs.getString("ID_USER") + "");
                this.penelitian.setIdKetuaPeneliti(rs.getString("ID_USER"));
                this.penelitian.setSumberDana(sumberPendanaan);
                this.penelitian.setDanaDiterima(rs.getString("BESAR_DANA_DITERIMA"));
                this.penelitian.setTanggalPenelitianDimulai(rs.getString("TANGGAL_PENELITIAN_DIMULAI"));
                this.penelitian.setTanggalPenelitianSelesai(rs.getString("TANGGAL_PENELITIAN_SELESAI"));

                this.penelitian.setLampiranLaporan(rs.getString("LAMPIRAN_LAPORAN_PENELITIAN"));
                this.penelitian.setTanggalUploadLaporan(rs.getString("TANGGAL_UPLOAD_LAPORAN"));
                this.penelitian.setUkuranLampiranLaporan(rs.getString("UKURAN_LAMPIRAN_LAPORAN_PENELITIAN"));
                this.penelitian.setTipeLampiranLaporan(rs.getString("TIPE_FILE_LAMPIRAN_LAPORAN_PENELITIAN"));
                                
                this.penelitian.setStatusPenelitian(rs.getString("STATUS_PENELITIAN"));
                this.penelitian.setIsiRevisi(rs.getString("ISI_REVISI_PENELITIAN"));
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return this.penelitian;

    }

    //method digunakan untuk mengubah data penelitian
    public boolean ubahDataPenelitian(Penelitian penelitian) {

        boolean hasil = false;
        this.penelitian = penelitian;

        String sql = "update penelitian_dosen SET "
                + "ID_SUMBER_PENDANAAN = " + this.penelitian.getSumberDana().getIdSumberPendanaan()
                + ", JUDUL_PENELITIAN = '" + this.penelitian.getJudulPenelitian()
                + "', TANGGAL_PENELITIAN_DIMULAI = '" + this.penelitian.getTanggalPenelitianDimulai()
                + "', TANGGAL_PENELITIAN_SELESAI = '" + this.penelitian.getTanggalPenelitianSelesai()
                + "', BESAR_DANA_DITERIMA = '" + this.penelitian.getDanaDiterima()
                + "', LAMPIRAN_LAPORAN_PENELITIAN = '" + this.penelitian.getLampiranLaporan()
                + "', TANGGAL_UPLOAD_LAPORAN = '" + this.penelitian.getTanggalUploadLaporan()
                + "', UKURAN_LAMPIRAN_LAPORAN_PENELITIAN = '" + this.penelitian.getUkuranLampiranLaporan()
                + "', TIPE_FILE_LAMPIRAN_LAPORAN_PENELITIAN = '" + this.penelitian.getTipeLampiranLaporan()
                + "', PENGUPLOAD = '" + this.penelitian.getPengupload().getIdUser()
                + "', STATUS_PENELITIAN = '" + this.penelitian.getStatusPenelitian()
                + "' where NO_PENELITIAN = " + this.penelitian.getNoPenelitian();
        System.out.println(sql);
        PreparedStatement st = null;
        Connection conn;
        try {
            conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }

    //method digunakan untuk menghapus data penelitian
    public boolean hapusDataPenelitian(Penelitian penelitian) {

        boolean hasil = true;

        PreparedStatement st = null;

        String sql = "DELETE FROM penelitian_dosen WHERE NO_PENELITIAN = " + penelitian.getNoPenelitian();

        Connection conn = Koneksi.getKoneksi();
        try {
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }
    
    //method digunakan untuk memvalidasi data penelitian oleh koordinator ppm
    public boolean validasiDataPenelitian(Penelitian penelitian) {

        boolean hasil = false;
        this.penelitian = penelitian;

        String sql = "update penelitian_dosen SET "                
                + " STATUS_PENELITIAN = '" + this.penelitian.getStatusPenelitian()
                + "', ISI_REVISI_PENELITIAN = '" + this.penelitian.getIsiRevisi()
                + "' where NO_PENELITIAN = " + this.penelitian.getNoPenelitian();
        System.out.println(sql);
        PreparedStatement st = null;
        Connection conn;
        try {
            conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }

    //method digunakan untuk mencetak rekapitulasi data mahasiswa yang terlibat dalam pelaksanaan penelitian
    public ArrayList rekapMahasiswaPeneliti(String tahunMulai, String tahunSelesai) {

        String sql = "SELECT penelitian_dosen.NO_PENELITIAN, JUDUL_PENELITIAN, NRP_MAHASISWA, NAMA_MAHASISWA, JUDUL_TUGAS_AKHIR, TANGGAL_PENELITIAN_DIMULAI, TANGGAL_PENELITIAN_SELESAI FROM "
                + "mahasiswa_peneliti, sumber_pendanaan, penelitian_dosen "
                + "WHERE penelitian_dosen.NO_PENELITIAN = mahasiswa_peneliti.NO_PENELITIAN "
                + "AND penelitian_dosen.ID_SUMBER_PENDANAAN = sumber_pendanaan.ID_SUMBER_PENDANAAN "
                + "AND STATUS_PENELITIAN = '1' ";

        if (!tahunMulai.isEmpty()) {
            sql = sql + "AND YEAR(TANGGAL_PENELITIAN_DIMULAI) BETWEEN '" + tahunMulai + "' AND '" + tahunSelesai + "'";
        }
        System.out.println(sql);
        ArrayList list = new ArrayList();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();
            int i = 1;
            while (rs.next()) {
                Map map = new HashMap();
                map.put("no", i);
                map.put("noPenelitian", rs.getString("NO_PENELITIAN"));
                map.put("judulPpm", rs.getString("JUDUL_PENELITIAN"));
                map.put("tanggalMulai", rs.getString("TANGGAL_PENELITIAN_DIMULAI"));
                map.put("tanggalSelesai", rs.getString("TANGGAL_PENELITIAN_SELESAI"));
                map.put("namaUser", rs.getString("NAMA_MAHASISWA"));
                map.put("nrpUser", rs.getString("NRP_MAHASISWA"));
                map.put("judulTugasAkhir", rs.getString("JUDUL_TUGAS_AKHIR"));

                list.add(map);
                i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenelitian.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;

    }
}
